-- Function: dems_ideas_report_quality_on_ets_detail_testfield(character varying, date, date)

-- DROP FUNCTION dems_ideas_report_quality_on_ets_detail_testfield(character varying, date, date);

CREATE OR REPLACE FUNCTION dems_ideas_report_quality_on_ets_detail_testfield(character varying, date, date)
  RETURNS SETOF report_quality_detail_type_testfield AS
$BODY$
DECLARE 
	v_schema_name alias for $1;
	v_startdate alias for $2;
	v_enddate alias for $3;
	v_error_ets int;	
	v_status_connect_dblink character varying(255); 	
	v_project record;
	--v_schema_name character varying;
	v_strQuery character varying;	
	v_ets_project record;
	v_step1 int;
	v_step2 int;
	v_step3 int;
	v_step_str character varying(4000) := '';
	v_project_id int;
	v_project_id_ets int;
	v_str_column_name_key_select character varying(255) := '';
	v_column_name_key record;
	v_strQuery1 character varying := '';	
	v_temp_donothing int;
	v_temp_donothing1 int;
begin
-----------select * from dems_ideas_report_quality_on_ets_detail_testfield('michelin','2014-04-01','2013-04-05');
--           SELECT * into report_quality_detail_type_testfield_1 from dems_ideas_report_quality_on_ets_detail_testfield('shell_cee','2013-07-01','2013-07-30');
--	     SELECT * from report_quality_detail_type_testfield_1;
--	SELECT distinct * from report_quality_detail1 where error_amount = 1
		BEGIN					
			v_status_connect_dblink := (SELECT dblink_connect('cnn','dbname=tracker_system port=5432 host=10.10.5.46 user=dems password=dems@fra'));
			EXCEPTION WHEN others THEN  
				IF (SELECT dblink_get_connections() is not null) THEN        
					PERFORM public.dblink_disconnect ('cnn');	     	
				END IF;
				v_status_connect_dblink := (SELECT dblink_connect('cnn','dbname=tracker_system port=5432 host=10.10.5.46 user=dems password=dems@fra'));
		END;
if(v_status_connect_dblink = 'OK') then 		
	drop table if exists report_quality_detail1;
	create table report_quality_detail1
	(
	    project_id integer,
	    project_name character varying(255),
	    managementid integer,
	    full_file_name character varying(255),    
	    user_id integer,
	    user_name character varying(255),
	    section_name character varying(50),
	    step character varying(50),
	    error_amount integer,
	    quantity integer,    
	    report_date date,
	    field_id integer,
	    field_name character varying(255),
	    data1 character varying,
	    data2 character varying,
	    user_id1 integer,
	    user_id2 integer,
	    user_name1 character varying(255),
	    user_name2 character varying(255),
	    data3 character varying,
	    user_id3 integer,
	    user_name3 character varying(255),
	    correct_data character varying,
	    step_3 boolean,
	    form_id int	
	);
	
	ALTER TABLE report_quality_detail1 OWNER TO dems;
	
	-------------Lay danh sach du an ben commonlogin-------------------------------------------------------------------------------------------
		
	v_project_id := (SELECT id from dblink('dbname=common_production_v1_2 port=5432 host=10.10.5.26 user=dems password=dems@fra',
	'SELECT a.id from production.tblproject a left join production.tblparent_project b on (a.parent_project = b.id) where 
	--coalesce(b.parent_project_name,'''') <> ''Testing'' and coalesce(b.parent_project_name,'''') <> ''Training'' and 
	a.is_ideas=1 
	and a.status = 1 and a.db_schema='''||v_schema_name||'''')
	 as a (id int));

	
	 ----- Kiem tra du an thu co QC ben EST hay khong
	if(v_schema_name is not null and v_schema_name <> '' and v_project_id in (select id from production.project where substr(processing,10,1)='0')
	and v_project_id in (SELECT cls_project_id from dblink('cnn','SELECT cls_project_id from tracker_system.project where import=false') as (cls_project_id int))) then

	------ Kiem tra cac buoc nhap cua du an
	select substring(processing,7,1),substring(processing,8,1),substring(processing,9,1)
	from production.project where id  = v_project_id into v_step1,v_step2,v_step3;	
	
	raise notice 'v_project_id: %',v_project_id;
	
	---------------------------------------Lay ra danh sach card qua QC tu ETS------------------------------------------
	
	-----------v_project Record: chua danh sach cac du an duoc lay tu ETS cua cung 1 schema_name.
	
	FOR v_project in SELECT project_id as project_id_ets from dblink('cnn','SELECT project_id from tracker_system.project where import=false and cls_project_id='||v_project_id)
	as (project_id int) 
	
	loop
		v_str_column_name_key_select := '';
		v_strQuery1 := '';
		v_project_id_ets := v_project.project_id_ets;	
		
		raise notice 'v_project_id_ets: %',v_project_id_ets;

		-------------------Lay ra danh sach key value ben ETS de map voi key field ben Ideas 
		
		select 1 from dblink('cnn','select tracker_system.data_entry_qc_ets_get_all_qc_detail('||v_project_id_ets||', '''||v_startdate||''','''||v_enddate||''')') as a (id int) into v_temp_donothing;	
			FOR v_column_name_key 
				IN SELECT column_name_map from dblink ('cnn','select distinct c.column_name_map FROM tracker_system.template_column a, tracker_system.project b ,
							tracker_system.project_column_keyname c
						      WHERE a.template_id = b.template_id and b.project_id = '||v_project_id_ets||' and a.is_key = true
						      and b.project_id = c.project_id and a.column_name = c.column_name') as a (column_name_map character varying(255))
				LOOP			       
					  v_str_column_name_key_select := v_str_column_name_key_select||' '||v_column_name_key.column_name_map||' text,';
					   v_strQuery1 := v_strQuery1 ||' and b.'||replace(replace(replace(v_column_name_key.column_name_map,'"',''),'(','("'),')','")')||'::text 
					   = a.'||replace(replace(replace(v_column_name_key.column_name_map,'"',''),'(','("'),')','")')||'::text';
			
					   
			END LOOP;		
		if(v_str_column_name_key_select <> '') then
		
			v_str_column_name_key_select := substring((replace(replace(v_str_column_name_key_select,'(',''),')','')),1,length((replace(replace(v_str_column_name_key_select,'(',''),')','')))-1);
			
				v_strQuery :='CREATE TABLE dataentry_ets AS 
						SELECT  b.full_file_name,b.management_id,a.qc_card_id FROM (
						select * from dblink(''cnn'',''select * from dataentry_ets'') 
						as a (qc_card_id text,qc_date text,qc_user text,export_date text , export_times int, '||v_str_column_name_key_select||')) as a,
						'||v_schema_name||'.data as b where 1 = 1 '||v_strQuery1;
						
						
			raise notice 'dataentry_ets: %',v_strQuery;
			
		----------------------Tao bang 	dataentry_ets chua danh sach cac card da thong qua QC
			drop table if exists dataentry_ets;
			execute v_strQuery;
		else
			truncate table dataentry_ets;			
		end if;
	--------------------------------------------------------End lay tong so card duoc QC tu ETS-------------------------------------	
		drop table if exists quality_typist_error1;					
		create table quality_typist_error1 
		(
			project_id int, 
			project_name character varying,
			full_file_name character varying,
			field_name character varying,
			typist_data character varying,
			qc_data character varying,
			export_date character varying
		);	
		ALTER TABLE quality_typist_error1 OWNER TO dems;
		
		----------Lay du lieu tung du an, cac card bi loi cua tung du an do vao 1 table quality_typist_error1
		----------Danh sach cac card boi loi se duoc get tu query qc_manual_get_all_detail_error() ben ETS
		insert into quality_typist_error1
		select v_project_id,v_schema_name,a.full_file_name,b.field_name,b.typist_data,b.qc_data,b.export_date
		fRom dataentry_ets a inner join (
		SELECT * from dblink ('cnn','SELECT full_file_name,field_name,typist_data,qc_data,export_date,qc_date,qc_card_id
		from tracker_system.qc_manual_get_all_detail_error_eon('||v_project_id_ets||','''||v_startdate||''','''||v_enddate||''')') 
				as (full_file_name character varying,field_name character varying,typist_data character varying,
				qc_data character varying,export_date character varying,qc_date character varying,qc_card_id text)
				) as b on (a.qc_card_id = b.qc_card_id);
		/*SELECT v_project_id,v_schema_name,* from dblink ('cnn','SELECT full_file_name,field_name,typist_data,qc_data,export_date
		from tracker_system.qc_manual_get_all_detail_error('||v_project_id_ets||','''||v_startdate||''','''||v_enddate||''')') 
				as (full_file_name character varying,field_name character varying,typist_data character varying,
				qc_data character varying,export_date character varying);	*/			
				
				drop table if exists quality_data_difference_report1;
				CREATE TABLE quality_data_difference_report1
				(
				  id serial NOT NULL,
				  data_id integer,
				  field_id integer,
				  field_name character varying,			  
				  full_file_name character varying,
				  data1 character varying,
				  data2 character varying,
				  user_id1 integer,
				  user_id2 integer,
				  user_name1 character varying(255),
				  user_name2 character varying(255),
				  created_time character varying(10),
				  management_id integer,			  
				  data3 character varying,
				  user_id3 integer,
				  user_name3 character varying(255),
				  correct_data character varying,
				  section_name character varying				 		  			 
				);		
				---Set owner table quality_data_difference_report1 sang dems
				ALTER TABLE quality_data_difference_report1 OWNER TO dems;
				
					/*Lay loi vao bang quality_data_difference_report1: lay tu bang data_difference_report va data_difference_report1*/					
					v_strQuery := '
					INSERT INTO quality_data_difference_report1(
					data_id,field_id,field_name,full_file_name,data1,data2,user_id1,user_id2,user_name1,user_name2,
					management_id,data3,user_id3,user_name3,correct_data,section_name)
					SELECT c.data_id,c.field_id,c.field_name,c.full_file_name,c.data1,c.data2,c.user_id1,c.user_id2,c.user_name1,user_name2,
					c.management_id,c.data3,c.user_id3,c.user_name3,b.qc_data,c.section_name				
					FROM '||v_schema_name||'.data a, quality_typist_error1 b,
					'||v_schema_name||'.data_difference_report c
					WHERE a.full_file_name = b.full_file_name
					AND a.id = c.data_id AND b.field_name = lower(c.field_name)
					ORDER By a.id asc';	
					
					raise notice '1. %',v_strQuery;			
					execute v_strQuery;
					v_strQuery := 'DROP TABLE IF EXISTs tmp_project;
							create temp table tmp_project
							as SELECT a.id,a.management_id, b.*,c.field_id,c.input_section
					 FROM '||v_schema_name||'.data a, quality_typist_error1 b,'||v_schema_name||'.field c,'||v_schema_name||'.management m
					 WHERE 
						a.management_id = m.id
						AND lower(a.full_file_name) = lower(b.full_file_name)
						AND lower(b.field_name) = lower(c.field_name) and c.step = 1
						AND a.id::text||b.field_name not in (SELECT data_id::text||lower(field_name) 
						from '||v_schema_name||'.data_difference_report);';
					execute v_strQuery;

					
					v_strQuery := '
					INSERT INTO quality_data_difference_report1(data_id,field_id,field_name,full_file_name,data1,data2,user_id1,
					user_id2,user_name1,user_name2,management_id,data3,user_id3,user_name3,correct_data,section_name)
					SELECT a.data_id,a.field_id,a.field_name,a.full_file_name,a.data1,b.data2,a.user_id1,b.user_id2,a.user_name1,b.user_name2
					,a.managementid,c.data3,c.user_id3,c.user_name3,a.qc_data as correct_data,a.sectionname as section_name  
					FROM 
					(
					SELECT  distinct b.id as data_id,b.field_id,b.field_name,b.full_file_name,b.typist_data as data1,
					a.userid as user_id1,a.username as user_name1,a.managementid,b.qc_data,a.sectionname
					FROM '||v_schema_name||'.session as a,
					
					( select * from tmp_project)
						as b
						WHERE a.managementid = b.management_id and a.inputstep = 1  AND a.sectionid = b.input_section
						ORDER BY a.managementid asc
						) as a
					LEFT JOIN 
					(
					SELECT distinct  b.id as data_id,b.field_id,b.field_name,b.full_file_name,b.typist_data as data2,a.userid as user_id2,a.username as user_name2,a.managementid,b.qc_data,a.sectionname
					FROM '||v_schema_name||'.session as a, 
					(
					select * from tmp_project
					)as b
						WHERE a.managementid = b.management_id and a.inputstep = 2  AND a.sectionid = b.input_section
						ORDER BY a.managementid asc
						) as b on (a.data_id = b.data_id) AND a.sectionname = b.sectionname and a.field_name = b.field_name
					LEFT JOIN
					(
					SELECT distinct b.id as data_id,b.field_id,b.field_name,b.full_file_name,b.typist_data as data3,a.userid as user_id3,a.username as user_name3,
					a.managementid,	b.qc_data,a.sectionname
					from '||v_schema_name||'.session as a,
					( 
						select * from tmp_project
					)
						as b
						WHERE a.managementid = b.management_id and a.inputstep =3  AND a.sectionid = b.input_section
						ORDER BY a.managementid asc
						) as c on (a.data_id = c.data_id) and a.sectionname = c.sectionname and a.field_name = c.field_name
					';
					raise notice '2. %',v_strQuery;	
					execute v_strQuery;
					
					----- step = 0: chi nhap 1 buoc
					v_strQuery := 'DROP TABLE IF EXISTS tmp_project_one_step;
					CREATE TEMP TABLE tmp_project_one_step as
					SELECT distinct a.id,a.management_id, b.*,c.field_id,c.input_section
							FROM '||v_schema_name||'.data a, quality_typist_error1 b,'||v_schema_name||'.field c,
							'||v_schema_name||'.management m
							WHERE a.management_id = m.id
							AND lower(a.full_file_name) = lower(b.full_file_name)
							AND lower(b.field_name) = lower(c.field_name) and c.step = 0 
							AND a.id::text||b.field_name not in 
							(SELECT data_id::text||lower(field_name) from '||v_schema_name||'.data_difference_report);';
					execute v_strQuery;		
					v_strQuery := 
					'insert into quality_data_difference_report1(data_id,field_id,field_name,full_file_name,data1,data2,user_id1,user_id2,user_name1,user_name2,
					management_id,data3,user_id3,user_name3,correct_data,section_name)
					SELECT distinct a.data_id,a.field_id,a.field_name,a.full_file_name,a.data1,'''' as data2,a.user_id1,0 as user_id2,a.user_name1,'''' as user_name2
					,a.managementid,'''' as data3,0 as user_id3,'''' as user_name3,a.qc_data as correct_data,a.sectionname as section_name from 
					(
					SELECT b.id as data_id,b.field_id,b.field_name,b.full_file_name,b.typist_data as data1,a.userid as user_id1,
					a.username as user_name1,a.managementid,b.qc_data,a.sectionname
					FROM '||v_schema_name||'.session as a, 
						(
							select * from tmp_project_one_step
						)as b
					WHERE a.managementid = b.management_id AND a.sectionid = b.input_section
					ORDER BY a.managementid asc
					) as a';
						
						
					raise notice '3. %',v_strQuery;	
					execute v_strQuery;
					if(v_step3 = 0 and v_step2 =0) then 
					raise notice 'Du an 1 buoc';
						v_strQuery:=
						'insert into report_quality_detail1 
						SELECT '||v_project_id||' AS project_id,'''||v_schema_name||''' AS schema_name,managementid,full_file_name, 
							user_id, user_name, section_name, step, has_error as error_amount,0 as quantity,starttime AS report_date,
							field_id,field_name,data1,data2,user_id1,user_id2,user_name1,user_name2,data3,user_id3,user_name3,correct_data,step_3,form_id
						FROM 
						(
							SELECT distinct ss.managementid,m.full_file_name,ss.userid AS user_id, ss.fullname AS user_name, ss.sectionid AS section_id, ss.sectionname AS section_name,
								ss.inputstep AS step,1 AS has_error,ss.starttime::date,ddr.field_id,field_name,data1,data2,user_id1,user_id2,user_name1,user_name2,
								data3,user_id3,user_name3,correct_data,false AS step_3,m.form_id
							FROM '||v_schema_name||'.session ss,'||v_schema_name||'.management m,quality_data_difference_report1 ddr
							WHERE ss.managementid = m.id AND ddr.management_id = m.id
							
							-------Khong tinh nhung hidden_field.
							
								AND not exists (SELECT 1 from '||v_schema_name||'.field WHERE hidden = TRUE and ddr.field_id = field_id  and form_id = m.form_id)
								
								AND m.omr_verified_status = 1 AND ss.inputstep in (1,2,3,5) AND m.record_status = 5 AND 														
								--AND date(ss.starttime) >= date('''||v_startdate||''') AND date(ss.starttime) <= date('''||v_enddate||''') 
								AND 1=1 						
								AND coalesce(ss.sectionname) = coalesce(ddr.section_name, '''') 
								AND (
								(ss.inputstep = 1 AND ddr.user_id1 = ss.userid AND data1 IS NOT NULL AND trim(data1) != trim(correct_data)) 
								OR (ss.inputstep = 2 AND ddr.user_id2 = ss.userid AND data2 IS NOT NULL AND trim(data2) != trim(correct_data)) 
								OR (ss.inputstep = 3 AND ddr.user_id3 = ss.userid AND data3 IS NOT NULL AND trim(data3) != trim(correct_data)) 
								OR (ss.inputstep = 5 AND ddr.user_id1 = ss.userid))								
								) AS tmp
							

							UNION

							SELECT managementid,full_file_name,tmp.user_id, tmp.user_name, tmp.section_name, tmp.step,0 as error_amount,1 as quantity,tmp.starttime,
								null AS field_id,'''' AS field_name,'''' AS data1,'''' AS data2,null AS user_id1,null AS user_id2,'''' AS user_name1,
								'''' AS user_name2, '''' AS data3,null AS user_id3,'''' AS user_name3,'''' AS correct_data,false AS step_3,form_id
							FROM (							
								SELECT distinct ss.managementid,m.full_file_name, ss.userid AS user_id, ss.fullname AS user_name, ss.sectionid AS section_id, ss.sectionname AS section_name, 
									ss.inputstep AS step,ss.starttime::date, 0 AS error_amount,m.form_id
								FROM '||v_schema_name||'.session ss, '||v_schema_name||'.management m
								WHERE ss.managementid = m.id AND m.omr_verified_status = 1 AND ss.inputstep in (1,2,3,5) AND m.record_status = 5 
								AND m.id in (select management_id from dataentry_ets union select 0)
								--AND date(ss.starttime) >= date('''||v_startdate||''') AND date(ss.starttime) <= date('''||v_enddate||''')
							
							      ) as tmp 
						) AS a';
					else
						raise notice 'Du an qua 2, 3 buoc';
						v_strQuery := '
						drop table if exists tmp;
						create temp table tmp as
						SELECT distinct ss.managementid,m.full_file_name,ss.userid as user_id, ss.fullname as user_name, ss.sectionid as section_id, ss.sectionname as section_name,
									ss.inputstep as step,1 as has_error,ss.starttime::date,ddr.field_id,field_name,data1,data2,user_id1, user_id2,user_name1,user_name2,
									data3,user_id3,user_name3,correct_data,m.form_id
								FROM '||v_schema_name||'.session ss,'||v_schema_name||'.management m,'||v_schema_name||'.data_difference_report ddr
								WHERE ss.managementid = m.id AND m.omr_verified_status = 1 
								
								-------Khong tinh nhung hidden_field----------------------																
									
									AND ss.inputstep in (1,2,3,5) AND m.record_status = 5 
									AND m.id not in (select management_id from dataentry_ets union select 0)											
									AND date(ss.starttime) >= date('''||v_startdate||''') AND date(ss.starttime) <= date('''||v_enddate||''') AND 1=1 
									AND ddr.management_id = m.id 
									AND coalesce(ss.sectionname) = coalesce(ddr.section_name, '''') 
									AND (
									(ss.inputstep = 1 AND ddr.user_id1 = ss.userid AND data1 is not null AND trim(data1) != trim(correct_data) AND field_type = 0) 
									OR (ss.inputstep = 2 AND ddr.user_id2 = ss.userid AND data2 is not null AND trim(data2) != trim(correct_data) AND field_type = 0) 
									OR (ss.inputstep = 3 AND ddr.user_id3 = ss.userid AND data3 is not null AND trim(data3) != trim(correct_data) AND field_type = 0) 
									OR (ss.inputstep = 5 AND ddr.user_id1 = ss.userid AND field_type = 1));
							';execute v_strQuery;		
							v_strQuery := '
							drop table if exists tmp_project;
							create temp table tmp_project as
							select * from tmp a 
								where not exists (SELECT 1 from '||v_schema_name||'.field WHERE hidden = TRUE 
								and a.field_id = field_id  and form_id = a.form_id)';
						execute v_strQuery;
							
						v_strQuery:= 
						'insert into report_quality_detail1
						SELECT '||v_project_id||' as project_id,'''||v_schema_name||''' as schema_name,managementid,full_file_name, 
							user_id, user_name, section_name, step, error_amount,quantity,starttime  as report_date,
							field_id,field_name,data1,data2,user_id1, user_id2,user_name1,user_name2,data3,user_id3,user_name3,correct_data,step_3,form_id
						FROM 
						(
							SELECT managementid,full_file_name,tmp.user_id, tmp.user_name, tmp.section_name, tmp.step,0 as error_amount,1 as quantity,tmp.starttime, 
								null as field_id,'''' as field_name,'''' as data1,'''' as data2,null as user_id1,null as user_id2,'''' as user_name1,
								'''' as user_name2, '''' as data3,null as user_id3,'''' as user_name3,'''' as correct_data,false as step_3,form_id
							FROM (
						
								SELECT distinct ss.managementid,m.full_file_name, ss.userid as user_id, ss.fullname as user_name, ss.sectionid as section_id, ss.sectionname as section_name, 
									ss.inputstep as step,ss.starttime::date, 0 as error_amount,m.form_id
								FROM '||v_schema_name||'.session ss, '||v_schema_name||'.management m
								WHERE ss.managementid = m.id and m.omr_verified_status = 1 and ss.inputstep in (1,2,3,5) and m.record_status = 5 
								AND m.id in (select management_id from dataentry_ets union select 0)
									--and date(ss.starttime) >= date('''||v_startdate||''') and date(ss.starttime) <= date('''||v_enddate||''') 
								AND 1=1 
						
							      ) as tmp 

							UNION 

							SELECT managementid,full_file_name, tmp.user_id, tmp.user_name, tmp.section_name, tmp.step, tmp.has_error as error_amount,0 as quantity,tmp.starttime,
								field_id,field_name,data1,data2,user_id1, user_id2,user_name1,user_name2,data3,user_id3,user_name3,correct_data, step_3,form_id
							FROM 
							(
								SELECT distinct ss.managementid,m.full_file_name, ss.userid as user_id, ss.fullname as user_name, ss.sectionid as section_id, ss.sectionname as section_name,
									ss.inputstep as step,1 as has_error,ss.starttime::date,ddr.field_id,field_name,data1,data2,user_id1, user_id2,user_name1,user_name2,
									data3,user_id3,user_name3,correct_data,false as step_3,m.form_id
								FROM '||v_schema_name||'.session ss,'||v_schema_name||'.management m,quality_data_difference_report1 ddr
								WHERE ss.managementid = m.id AND ddr.management_id = m.id AND m.omr_verified_status = 1 
								AND ss.inputstep in (1,2,3,5) and m.record_status = 5 	
								-------Khong tinh nhung hidden_field----------------------
								
									AND not exists (SELECT 1 from '||v_schema_name||'.field WHERE hidden = TRUE and ddr.field_id = field_id  and form_id = m.form_id)
																										
									--AND date(ss.starttime) >= date('''||v_startdate||''') and date(ss.starttime) <= date('''||v_enddate||''') 
									AND 1=1 						
									AND coalesce(ss.sectionname) = coalesce(ddr.section_name, '''') 
									AND (
									(ss.inputstep = 1 AND ddr.user_id1 = ss.userid AND data1 is not null AND trim(data1) != trim(correct_data)) 
									OR (ss.inputstep = 2 AND ddr.user_id2 = ss.userid AND data2 is not null AND trim(data2) != trim(correct_data)) 
									OR (ss.inputstep = 3 AND ddr.user_id3 = ss.userid AND data3 is not null AND trim(data3) != trim(correct_data)) 
									OR (ss.inputstep = 5 AND ddr.user_id1 = ss.userid))		
						 
							) as tmp
							
							UNION

-------------------------------------------------------------Card qua QC nhung khong loi, tinh loi cho buoc 3 *07-----------------------------------------------

							
							SELECT managementid,full_file_name, tmp.user_id, tmp.user_name, tmp.section_name, tmp.step, tmp.has_error as error_amount,0 as quantity,tmp.starttime,
								field_id,field_name,data1,data2,user_id1, user_id2,user_name1,user_name2,data3,user_id3,user_name3,correct_data,true as step_3,form_id
							FROM 
							(
								SELECT distinct ss.managementid,m.full_file_name,ss.userid as user_id, ss.fullname as user_name, ss.sectionid as section_id, ss.sectionname as section_name,
									ss.inputstep as step,1 as has_error,ss.starttime::date,ddr.field_id,field_name,data1,data2,user_id1, user_id2,user_name1,user_name2,
									data3,user_id3,user_name3,correct_data,m.form_id
								FROM '||v_schema_name||'.session ss,'||v_schema_name||'.management m,'||v_schema_name||'.data_difference_report ddr
								WHERE ss.managementid = m.id AND m.omr_verified_status = 1 
								
								-------Khong tinh nhung hidden_field----------------------
								
									AND not exists (SELECT 1 from '||v_schema_name||'.field WHERE hidden = TRUE and ddr.field_id = field_id  and form_id = m.form_id)
									
									AND ss.inputstep in (1,2,3,5) AND m.record_status = 5 
									AND m.id in (select management_id from dataentry_ets where management_id
									not in (select management_id from quality_data_difference_report1) union select 0)
																				
									--AND date(ss.starttime) >= date('''||v_startdate||''') AND date(ss.starttime) <= date('''||v_enddate||''') 
									
									AND 1=1 
									AND ddr.management_id = m.id 
									AND coalesce(ss.sectionname) = coalesce(ddr.section_name, '''') 
									AND (
									(ss.inputstep = 1 AND ddr.user_id1 = ss.userid AND data1 is not null AND trim(data1) != trim(correct_data) AND field_type = 0) 
									OR (ss.inputstep = 2 AND ddr.user_id2 = ss.userid AND data2 is not null AND trim(data2) != trim(correct_data) AND field_type = 0) 
									OR (ss.inputstep = 3 AND ddr.user_id3 = ss.userid AND data3 is not null AND trim(data3) != trim(correct_data) AND field_type = 0) 
									OR (ss.inputstep = 5 AND ddr.user_id1 = ss.userid AND field_type = 1))		
							 
							) as tmp
						
							UNION	
							
---------------------------------------------------------Card loi qua buoc 3---------------------------------------------------------------------------------------

							SELECT managementid,full_file_name,tmp.user_id, tmp.user_name, tmp.section_name, tmp.step,0 as error_amount, 1 as quantity,tmp.starttime, 
								null as field_id,'''' as field_name,'''' as data1,'''' as data2,null as user_id1,null as user_id2,'''' as user_name1,
								'''' as user_name2, '''' as data3,null as user_id3,'''' as user_name3,'''' as correct_data, true as step_3,form_id
							FROM (
								SELECT distinct ss.managementid,m.full_file_name, ss.userid as user_id, ss.fullname as user_name, ss.sectionid as section_id, ss.sectionname as section_name, 
									ss.inputstep as step,ss.starttime::date, 0 as error_amount,m.form_id
								FROM '||v_schema_name||'.session ss, '||v_schema_name||'.management m
								WHERE ss.managementid = m.id AND m.omr_verified_status = 1 AND ss.inputstep in (1,2,3,5) AND m.record_status = 5 
								AND m.id not in (select management_id from dataentry_ets union select 0)											
								AND date(ss.starttime) >= date('''||v_startdate||''') AND date(ss.starttime) <= date('''||v_enddate||''')
								
							     ) as tmp 
						
							UNION 

							SELECT managementid,full_file_name, tmp.user_id, tmp.user_name, tmp.section_name, tmp.step, tmp.has_error as error_amount,0 as quantity,tmp.starttime,
								field_id,field_name,data1,data2,user_id1, user_id2,user_name1,user_name2,data3,user_id3,user_name3,correct_data,true as step_3,form_id
							FROM 
							(
								select * from tmp_project		
							 
							) as tmp
							
						
---------------------------------------------------------END Card loi qua buoc 3---------------------------------------------------------------------------------------						
						) AS a';
					end if;
					raise notice 'END: %',v_strQuery;	
					execute v_strQuery;
				end loop;
			end if;
	--end loop;
	v_strQuery := 'SELECT distinct * from report_quality_detail1 a where error_amount = 1 and exists 
				(select 1 
				from '||v_schema_name||'.field where a.form_id::text = form_id::text and a.field_id::text = field_id::text and
				lower(a.field_name) = lower(field_name) and a.step::int = ANY ((case when step = 0 then ''{1}''::int[] else ''{1,2,3,5}''::int[] end)))
			union 
			SELECT distinct * from report_quality_detail1 where error_amount = 0
			ORDER BY user_id,report_date asc;';
	RETURN QUERY
	execute v_strQuery;		
end if;
PERFORM dblink_disconnect('cnn');
END;	
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION dems_ideas_report_quality_on_ets_detail_testfield(character varying, date, date)
  OWNER TO postgres;

